/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package project3;

import java.sql.*;
import java.util.Properties;
import java.util.*;
import javax.xml.stream.*;
import java.io.*;
import javax.swing.*;

/**
 *
 * @author Kevin
 */
public class RetrieveDatabaseInfo {

    public static String db = "";
    public static String pw = "";
    public static Connection conn = null;

    public RetrieveDatabaseInfo(){
        connDB();
    }

        /**
         * connDB - gets the connection information from the users.properties file
         * @return void
         */
        public static void connDB(){
        Properties prop = new Properties();
        try {
            //load a properties file
                prop.load(new FileInputStream("user.properties"));
                db = prop.getProperty("db");
                pw = prop.getProperty("pw");

        } catch (IOException ex) {
                ex.printStackTrace();
        }
        }

	public void createConnection(){
	    try{
		System.out.println("Starting connection");
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		String  dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/" + db;
		System.out.println(" Attempting Connections... ");
		conn = DriverManager.getConnection(dbURL,db,pw);
		System.out.println(" Connection Successful! ");
	    }
	    catch(Exception e) { e.printStackTrace();}

	}

	public void closeConnection(){
	    try{
		conn.close();
	    }
	    catch(java.sql.SQLException ex){
            System.out.println("SQL error");
        }
	}



        /******************************************************************************

         Beginning Sample Option One Queries

         ******************************************************************************/


	/**
         * getOptionOneOrThreeSampleCountry - Finds countries within specified times and outputs result into JTextArea.
	 * @param JTextArea The JTextArea that the results will be written to.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
	 * @param Integer Query option(1 or 3).
         * @return boolean of whether or not the query failed.
         */
        public boolean getOptionOneOrThreeSampleCountry(JTextArea textArea, Timestamp startDate, Timestamp endDate, int option) throws SQLException{

            PreparedStatement ps = null;
            ResultSet rs = null;

            //for junit testing
            boolean failed = false;


            try{
                    



                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(country), country FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (viewdate > ?) AND (viewdate <= ?) GROUP BY country Order By country";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setTimestamp(1, startDate);
                    ps.setTimestamp(2, endDate);
                    rs = ps.executeQuery();



                System.out.println("Attempting to get country data");

                textArea.setText( textArea.getText() + "\nCountries: \n");


                int queryLimit = 0;
                while(rs.next() && queryLimit < 3){
                    queryLimit++;
                    String country = rs.getString("country");
                    if(country == null){ country = "No Data"; }
                    textArea.setText( textArea.getText() + "     " + country + ":  " + rs.getInt(1) + "\n");
                    getOptionOneSampleCity(textArea, country, startDate, endDate, option);
                }

                System.out.println("Country data retrieved");

                    ps.close();
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;
            }


	/**
         * getOptionOneSampleCity - Finds cities within specified times and outputs result into JTextArea.
	 * @param JTextArea The JTextArea that the results will be written to.
	 * @param String Country that the cities are in.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
	 * @param Integer Rhe query option(1 or 3).
         * @return boolean of whether or not the query failed.
         */
        private boolean getOptionOneSampleCity(JTextArea textArea, String country, Timestamp startDate, Timestamp endDate, int option){
                 PreparedStatement ps = null;
            ResultSet rs = null;

            //for junit testing
            boolean failed = false;


            try{



                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(city), city FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (country = ?) AND (viewdate > ?) AND (viewdate <= ?) GROUP BY city ORDER BY city";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, country);
                    ps.setTimestamp(2, startDate);
                    ps.setTimestamp(3, endDate);
                    rs = ps.executeQuery();


                System.out.println("Attempting to get city data");


                textArea.setText( textArea.getText() + "\n          Cities: \n");

                int queryLimit = 0;
                while(rs.next() && queryLimit < 3){
                    queryLimit++;
                    String city = rs.getString("city");
                    if(city == null){ city = "No Data"; }
                    textArea.setText( textArea.getText() +  "                " + city + ":  " + rs.getInt(1) + "\n");
                    if(option == 1){
                        getOptionOneSampleAlgorithm(textArea, country, city, startDate, endDate);
                    }
                    else{
                        getOptionThreeSampleUser(textArea, country, city, startDate, endDate);
                    }
                    
                }

                System.out.println("City data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;
            
        }

	/**
         * getOptionOneSampleAlgorithm - Finds algorithms within specified times, city and country and outputs result into JTextArea.
	 * @param JTextArea The JTextArea that the results will be written to.
	 * @param String Country that the cities are in.
	 * @param String City that the algorithms were accessed in.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
         * @return boolean of whether or not the query failed.
         */
        private boolean getOptionOneSampleAlgorithm(JTextArea textArea, String country, String city, Timestamp startDate, Timestamp endDate){
                 PreparedStatement ps = null;
            ResultSet rs = null;

            //for junit testing
            boolean failed = false;


            try{


                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(name), name FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (country = ?) AND (city = ?) AND (viewdate > ?) AND (viewdate <= ?) GROUP BY name ORDER BY name";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, country);
                    ps.setString(2, city);
                    ps.setTimestamp(3, startDate);
                    ps.setTimestamp(4, endDate);
                    rs = ps.executeQuery();


                System.out.println("Attempting to get algorithm data");

                textArea.setText( textArea.getText() + "\n               Algorithm Names: \n");

                int queryLimit = 0;
                while(rs.next() && queryLimit < 3){
                    queryLimit++;
                    String name = rs.getString("name");
                    if(name == null){ name = "No Data"; }
                    textArea.setText( textArea.getText() +  "                    " + name + ":  " + rs.getInt(1) + "\n");

                }

                System.out.println("Algorithm data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;

        }

        /******************************************************************************

         Beginning Sample Option Two Queries

         ******************************************************************************/


	/**
         * getOptionTwoSampleAlgorithm - Finds algorithms within specified times and outputs result into JTextArea.
	 * @param JTextArea The JTextArea that the results will be written to.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
         * @return boolean of whether or not the query failed.
         */
        public boolean getOptionTwoSampleAlgorithm(JTextArea textArea, Timestamp startDate, Timestamp endDate) throws SQLException{

            PreparedStatement ps = null;
            ResultSet rs = null;

            //for junit testing
            boolean failed = false;


            try{


                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(name), name FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (viewdate > ?) AND (viewdate <= ?) GROUP BY name ORDER BY name";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setTimestamp(1, startDate);
                    ps.setTimestamp(2, endDate);
                    rs = ps.executeQuery();


                System.out.println("Attempting to get algorithm data");

                textArea.setText( textArea.getText() + "\nAlgorithm Names: \n");

                int queryLimit = 0;
                while(rs.next() && queryLimit < 3){
                    queryLimit++;
                    String name = rs.getString("name");
                    if(name == null){ name = "No Data"; }
                    textArea.setText( textArea.getText() + "     " + name + ":  " + rs.getInt(1) + "\n");
                    getOptionTwoSampleCountry(textArea, name, startDate, endDate);;
                }

                System.out.println("Algorithm data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;
            }


	/**
         * getOptionTwoSampleCountry - Finds countries within specified times and has viewed a specified algorithm name and outputs result into JTextArea.
	 * @param JTextArea The JTextArea that the results will be written to.
	 * @param String Algorithm name.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
         * @return boolean of whether or not the query failed.
         */
        private boolean getOptionTwoSampleCountry(JTextArea textArea, String name, Timestamp startDate, Timestamp endDate){
                 PreparedStatement ps = null;
            ResultSet rs = null;

            //for junit testing
            boolean failed = false;


            try{


                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(country), country FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (name = ?) AND (viewdate > ?) AND (viewdate <= ?) GROUP BY country ORDER BY country";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, name);
                    ps.setTimestamp(2, startDate);
                    ps.setTimestamp(3, endDate);
                    rs = ps.executeQuery();



                System.out.println("Attempting to get country data");

                textArea.setText( textArea.getText() + "\n          Countries: \n");


                int queryLimit = 0;
                while(rs.next() && queryLimit < 3){
                    queryLimit++;
                    String country = rs.getString("country");
                    if(country == null){ country = "No Data"; }
                    textArea.setText( textArea.getText() +  "                " + country + ":  " + rs.getInt(1) + "\n");
                    getOptionTwoSampleCity(textArea, name, country, startDate, endDate);

                }

                System.out.println("Country data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;

        }

	/**
         * getOptionTwoSampleCity - Finds cities within specified times, country and has viewed a specified algorithm name and outputs result into JTextArea.
	 * @param JTextArea The JTextArea that the results will be written to.
	 * @param String Algorithm name.
	 * @param String Country the cities are in.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
         * @return boolean of whether or not the query failed.
         */
        private boolean getOptionTwoSampleCity(JTextArea textArea, String name, String country, Timestamp startDate, Timestamp endDate){
                 PreparedStatement ps = null;
            ResultSet rs = null;

            //for junit testing
            boolean failed = false;


            try{



                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(city), city FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (country = ?) AND (name = ?) AND (viewdate > ?) AND (viewdate <= ?) GROUP BY city ORDER BY city";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, country);
                    ps.setString(2, name);
                    ps.setTimestamp(3, startDate);
                    ps.setTimestamp(4, endDate);
                    rs = ps.executeQuery();



                System.out.println("Attempting to get city data");

                textArea.setText( textArea.getText() + "\n               Cities: \n");

                int queryLimit = 0;
                while(rs.next() && queryLimit < 3){
                    queryLimit++;
                    String city = rs.getString("city");
                    if(city == null){ city = "No Data"; }
                    textArea.setText(  textArea.getText() + "                    " + city + ":  " + rs.getInt(1) + "\n");

                }

                System.out.println("city data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;

        }


        /******************************************************************************

         Beginning Sample Option Three Queries
         * Note: The first two queries are shared with Option 1

         ******************************************************************************/


	/**
         * getOptionTwoSampleCity - Finds users within specified times, country, and city and outputs result into JTextArea.
	 * @param JTextArea The JTextArea that the results will be written to.
	 * @param String Country the city is in.
	 * @param String City the users are in.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
         * @return boolean of whether or not the query failed.
         */
        private boolean getOptionThreeSampleUser(JTextArea textArea, String country, String city, Timestamp startDate, Timestamp endDate){
                 PreparedStatement ps = null;
            ResultSet rs = null;

            //for junit testing
            boolean failed = false;


            try{



                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(IP.user), IP.user FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (country = ?) AND (city = ?) AND (viewdate > ?) AND (viewdate <= ?) GROUP BY IP.user ORDER BY IP.user";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, country);
                    ps.setString(2, city);
                    ps.setTimestamp(3, startDate);
                    ps.setTimestamp(4, endDate);
                    rs = ps.executeQuery();


                System.out.println("Attempting to get user data");

                textArea.setText( textArea.getText() + "\n               Users: \n");

                int queryLimit = 0;
                while(rs.next() && queryLimit < 3){
                    queryLimit++;
                    String user = rs.getString("IP.user");
                    if(user == null){ user = "No Data"; }
                    textArea.setText( textArea.getText() + "                    " + user + ":  " + rs.getInt(1) + "\n");

                }

                System.out.println("User data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;

        }



        /******************************************************************************

         Beginning Text File Option One Queries

         ******************************************************************************/


	/**
         * getOptionOneOrThreeTextFileCountry - Finds countries within specified times and outputs result into text file.
	 * @param PrintWriter Printwriter that the results will be written to.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
	 * @param Integer Query option(1 or 3).
         * @return boolean of whether or not the query failed.
         */
        public boolean getOptionOneOrThreeTextFileCountry(PrintWriter writer, Timestamp startDate, Timestamp endDate, int option) throws SQLException{

            PreparedStatement ps = null;
            ResultSet rs = null;

            //for junit testing
            boolean failed = false;


            try{


                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(country), country FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (viewdate > ?) AND (viewdate <= ?) GROUP BY country ORDER BY country";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setTimestamp(1, startDate);
                    ps.setTimestamp(2, endDate);
                    rs = ps.executeQuery();


                System.out.println("Attempting to get country data");


                writer.println("Countries:");


                while(rs.next()){
                    String country = rs.getString("country");
                    if(country == null){ country = "No Data"; }
                    writer.println("     " + country + ":  " + rs.getInt(1));
                    getOptionOneTextFileCity(writer, country, startDate, endDate, option);

                }

                System.out.println("Country data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;
            }


	/**
         * getOptionOneTextFileCity - Finds cities within specified times and outputs result into a text file.
	 * @param PrintWriter Printwriter that the results will be written to.
	 * @param String Country that the cities are in.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
	 * @param Integer Query option(1 or 3).
         * @return boolean of whether or not the query failed.
         */
        private boolean getOptionOneTextFileCity(PrintWriter writer, String country, Timestamp startDate, Timestamp endDate, int option){
                 PreparedStatement ps = null;
            ResultSet rs = null;


            //for junit testing
            boolean failed = false;


            try{



                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(city), city FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (country = ?) AND (viewdate > ?) AND (viewdate <= ?) GROUP BY city ORDER BY city";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, country);
                    ps.setTimestamp(2, startDate);
                    ps.setTimestamp(3, endDate);
                    rs = ps.executeQuery();


                System.out.println("Attempting to get city data");


                writer.println("          Cities:");

                while(rs.next()){
                    String city = rs.getString("city");
                    if(city == null){ city = "No Data"; }
                    writer.println("               " + city + ":  " + rs.getInt(1));
                    if(option == 1){
                        getOptionOneTextFileAlgorithm(writer, country, city, startDate, endDate);
                    }
                    else{
                        getOptionThreeTextFileUser(writer, country, city, startDate, endDate);
                    }

                    //Tuple tuple = new Tuple(rs.getString("city"), rs.getInt(1));
                   // cities.add(tuple);
                }

                System.out.println("City data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;

        }

	/**
         * getOptionOneTextFileAlgorithm - Finds algorithms within specified times, city and country and outputs result into a text file.
	 * @param PrintWriter Printwriter that the results will be written to.
	 * @param String Country that the cities are in.
	 * @param String City that the algorithms were accessed in.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
         * @return boolean of whether or not the query failed.
         */
        private boolean getOptionOneTextFileAlgorithm(PrintWriter writer, String country, String city, Timestamp startDate, Timestamp endDate){
                 PreparedStatement ps = null;
            ResultSet rs = null;

            //for junit testing
            boolean failed = false;


            try{



                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(name), name FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (country = ?) AND (city = ?) AND (viewdate > ?) AND (viewdate <= ?) GROUP BY name ORDER BY name";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, country);
                    ps.setString(2, city);
                    ps.setTimestamp(3, startDate);
                    ps.setTimestamp(4, endDate);
                    rs = ps.executeQuery();

                //rs.next();

                System.out.println("Attempting to get algorithm data");

                writer.println("               Algorithm Names:");

                while(rs.next()){
                    String name = rs.getString("name");
                    if(name == null){ name = "No Data"; }
                    writer.println("                    " + name + ":  " + rs.getInt(1));
                }

                System.out.println("Algorithm data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;

        }

        /******************************************************************************

         Beginning Text File Option Two Queries

         ******************************************************************************/


	/**
         * getOptionTwoTextFileAlgorithm - Finds algorithms within specified times and outputs result into a text file.
	 * @param PrintWriter Printwriter that the results will be written to.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
         * @return boolean of whether or not the query failed.
         */
        public boolean getOptionTwoTextFileAlgorithm(PrintWriter writer, Timestamp startDate, Timestamp endDate) throws SQLException{

            PreparedStatement ps = null;
            ResultSet rs = null;

            //for junit testing
            boolean failed = false;


            try{



                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(name), name FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (viewdate > ?) AND (viewdate <= ?) GROUP BY name ORDER BY name";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setTimestamp(1, startDate);
                    ps.setTimestamp(2, endDate);
                    rs = ps.executeQuery();



                System.out.println("Attempting to get algorithm data");

                writer.println("Algorithm Names:");

                while(rs.next()){
                    String name = rs.getString("name");
                    if(name == null){ name = "No Data"; }
                    writer.println("     " + name + ":  " + rs.getInt(1));
                    getOptionTwoTextFileCountry(writer, name, startDate, endDate);
                }

                System.out.println("Algorithm data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;
            }



	/**
         * getOptionTwoTextFileCountry - Finds countries within specified times and has viewed a specified algorithm name and outputs result into a text file.
	 * @param PrintWriter Printwriter that the results will be written to.
	 * @param String Algorithm name.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
         * @return boolean of whether or not the query failed.
         */
        private boolean getOptionTwoTextFileCountry(PrintWriter writer, String name, Timestamp startDate, Timestamp endDate){
                 PreparedStatement ps = null;
            ResultSet rs = null;

            //for junit testing
            boolean failed = false;


            try{
  

                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(country), country FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (name = ?) AND (viewdate > ?) AND (viewdate <= ?) GROUP BY country ORDER BY country";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, name);
                    ps.setTimestamp(2, startDate);
                    ps.setTimestamp(3, endDate);
                    rs = ps.executeQuery();



                System.out.println("Attempting to get country data");

                writer.println("          Countries:");


                while(rs.next()){
                    String country = rs.getString("country");
                    if(country == null){ country = "No Data"; }
                    writer.println("                " + country + ":  " + rs.getInt(1));
                    getOptionTwoTextFileCity(writer, name, country, startDate, endDate);

                }

                System.out.println("Country data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;

        }


	/**
         * getOptionTwoTextFileCity - Finds cities within specified times, country and has viewed a specified algorithm name and outputs result into a text file.
	 * @param PrintWriter Printwriter that the results will be written to.
	 * @param String Algorithm name.
	 * @param String Country the cities are in.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
         * @return boolean of whether or not the query failed.
         */
        private boolean getOptionTwoTextFileCity(PrintWriter writer, String name, String country, Timestamp startDate, Timestamp endDate){
                 PreparedStatement ps = null;
            ResultSet rs = null;


            //for junit testing
            boolean failed = false;


            try{




                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(city), city FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (country = ?) AND (name = ?) AND (viewdate > ?) AND (viewdate <= ?) GROUP BY city ORDER BY city";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, country);
                    ps.setString(2, name);
                    ps.setTimestamp(3, startDate);
                    ps.setTimestamp(4, endDate);
                    rs = ps.executeQuery();



                System.out.println("Attempting to get city data");

                writer.println("               Cities: ");

                while(rs.next()){
                    String city = rs.getString("city");
                    if(city == null){ city = "No Data"; }
                    writer.println("                    " + city + ":  " + rs.getInt(1));
                }

                System.out.println("city data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;

        }


        /******************************************************************************

         Beginning Text File Option Three Queries
         * Note: The first two queries are shared with Option 1

         ******************************************************************************/


	/**
         * getOptionTwoTextFileCity - Finds users within specified times, country, and city and outputs result into a text file.
	 * @param PrintWriter Printwriter that the results will be written to.
	 * @param String Country the city is in.
	 * @param String City the users are in.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
         * @return boolean of whether or not the query failed.
         */
        private boolean getOptionThreeTextFileUser(PrintWriter writer, String country, String city, Timestamp startDate, Timestamp endDate){
                 PreparedStatement ps = null;
            ResultSet rs = null;
            
            //for junit testing
            boolean failed = false;


            try{




                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(IP.user), IP.user FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (country = ?) AND (city = ?) AND (viewdate > ?) AND (viewdate <= ?) GROUP BY IP.user ORDER BY IP.user";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, country);
                    ps.setString(2, city);
                    ps.setTimestamp(3, startDate);
                    ps.setTimestamp(4, endDate);
                    rs = ps.executeQuery();


                System.out.println("Attempting to get user data");

                writer.println("               Users:");

                while(rs.next()){
                    String user = rs.getString("IP.user");
                    if(user == null){ user = "No Data"; }
                    writer.println("                    " + user + ":  " + rs.getInt(1));

                }

                System.out.println("User data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;

        }


        /******************************************************************************

         Beginning XML Option One Queries

         ******************************************************************************/

	/**
         * getOptionOneOrThreeXMLCountry - Finds countries within specified times and outputs result into XML file.
	 * @param XMLStreamWriter XMLStreamWriter that the results will be written to.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
	 * @param Integer The query option(1 or 3).
         * @return boolean of whether or not the query failed.
         */
        public boolean getOptionOneOrThreeXMLCountry(XMLStreamWriter xmlWriter, Timestamp startDate, Timestamp endDate, int option) throws SQLException{

            PreparedStatement ps = null;
            ResultSet rs = null;


            //for junit testing
            boolean failed = false;


            try{



                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT country, count(country) FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (viewdate > ?) AND (viewdate <= ?) GROUP BY country ORDER BY country";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setTimestamp(1, startDate);
                    ps.setTimestamp(2, endDate);
                    rs = ps.executeQuery();



                System.out.println("Attempting to get country data");

                


                while(rs.next()){
                    xmlWriter.writeStartElement("Country");
                    String country = rs.getString("country");
                    if(country == null){ country = "No Data";}
                    xmlWriter.writeAttribute("Name", country);
                    xmlWriter.writeAttribute("Views", Integer.toString(rs.getInt(2)) );
                    getOptionOneXMLCity(xmlWriter, country, startDate, endDate, option);
                    xmlWriter.writeEndElement();
                }

                System.out.println("Country data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;
            }



	/**
         * getOptionOneXMLCity - Finds cities within specified times and outputs result into XML file.
	 * @param XMLStreamWriter XMLStreamWriter that the results will be written to.
	 * @param String Country that the cities are in.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
	 * @param Integer The query option(1 or 3).
         * @return boolean of whether or not the query failed.
         */
        private boolean getOptionOneXMLCity(XMLStreamWriter xmlWriter, String country, Timestamp startDate, Timestamp endDate, int option){
                 PreparedStatement ps = null;
            ResultSet rs = null;


            //for junit testing
            boolean failed = false;


            try{




                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(city), city FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (country = ?) AND (viewdate > ?) AND (viewdate <= ?) GROUP BY city ORDER BY city";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, country);
                    ps.setTimestamp(2, startDate);
                    ps.setTimestamp(3, endDate);
                    rs = ps.executeQuery();



                System.out.println("Attempting to get city data");


                while(rs.next()){
                    String city = rs.getString("city");
                    if(city == null){ city = "No Data";}
                    xmlWriter.writeStartElement("City");
                    xmlWriter.writeAttribute("Name", city);
                    xmlWriter.writeAttribute("Views", Integer.toString(rs.getInt(1)) );
                    if(option == 1){
                        getOptionOneXMLAlgorithm(xmlWriter, country, city, startDate, endDate);
                    }
                    else{
                        getOptionThreeXMLUser(xmlWriter, country, city, startDate, endDate);
                    }

                    xmlWriter.writeEndElement();
                }

                System.out.println("City data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;

        }

	/**
         * getOptionOneXMLAlgorithm - Finds algorithms within specified times, city and country and outputs result into a XML file.
	 * @param XMLStreamWriter XMLStreamWriter that the results will be written to.
	 * @param String Country that the cities are in.
	 * @param String City that the algorithms were accessed in.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
         * @return boolean of whether or not the query failed.
         */
        private boolean getOptionOneXMLAlgorithm(XMLStreamWriter xmlWriter, String country, String city, Timestamp startDate, Timestamp endDate){
                 PreparedStatement ps = null;
            ResultSet rs = null;


            //for junit testing
            boolean failed = false;


            try{




                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(name), name FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (country = ?) AND (city = ?) AND (viewdate > ?) AND (viewdate <= ?) GROUP BY name ORDER BY name";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, country);
                    ps.setString(2, city);
                    ps.setTimestamp(3, startDate);
                    ps.setTimestamp(4, endDate);
                    rs = ps.executeQuery();



                System.out.println("Attempting to get algorithm data");



                while(rs.next()){
                    String name = rs.getString("name");
                    if(name == null){ name = "No Data";}
                    xmlWriter.writeStartElement("AlgorithmName");
                    xmlWriter.writeAttribute("Name", name);
                    xmlWriter.writeAttribute("Views", Integer.toString(rs.getInt(1)) );
                    xmlWriter.writeEndElement();
                }

                System.out.println("Algorithm data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;

        }

        /******************************************************************************

         Beginning XML Option Two Queries

         ******************************************************************************/


	/**
         * getOptionTwoXMLAlgorithm - Finds algorithms within specified times and outputs result into a XML file.
	 * @param XMLStreamWriter XMLStreamWriter that the results will be written to.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
         * @return boolean of whether or not the query failed.
         */
        public boolean getOptionTwoXMLAlgorithm(XMLStreamWriter xmlWriter, Timestamp startDate, Timestamp endDate) throws SQLException{

            PreparedStatement ps = null;
            ResultSet rs = null;

            //for junit testing
            boolean failed = false;


            try{



                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(name), name FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (viewdate > ?) AND (viewdate <= ?) GROUP BY name ORDER BY name";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setTimestamp(1, startDate);
                    ps.setTimestamp(2, endDate);
                    rs = ps.executeQuery();


                System.out.println("Attempting to get algorithm data");




                while(rs.next()){
                    String name = rs.getString("name");
                    if(name == null){ name = "No Data"; }
                    xmlWriter.writeStartElement("AlgorithmName");
                    xmlWriter.writeAttribute("Name", name);
                    xmlWriter.writeAttribute("Views", Integer.toString(rs.getInt(1)) );
                    
                    getOptionTwoXMLCountry(xmlWriter, name, startDate, endDate);
                    xmlWriter.writeEndElement();
                }

                System.out.println("Algorithm data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;
            }


	/**
         * getOptionTwoXMLCountry - Finds countries within specified times and has viewed a specified algorithm name and outputs result into a XML file.
	 * @param XMLStreamWriter XMLStreamWriter that the results will be written to.
	 * @param String Algorithm name.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
         * @return boolean of whether or not the query failed.
         */
        private boolean getOptionTwoXMLCountry(XMLStreamWriter xmlWriter, String name, Timestamp startDate, Timestamp endDate){
                 PreparedStatement ps = null;
            ResultSet rs = null;


            //for junit testing
            boolean failed = false;


            try{



                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(country), country FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (name = ?) AND (viewdate > ?) AND (viewdate <= ?) GROUP BY country ORDER BY country";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, name);
                    ps.setTimestamp(2, startDate);
                    ps.setTimestamp(3, endDate);
                    rs = ps.executeQuery();



                System.out.println("Attempting to get country data");




                while(rs.next()){
                    String country = rs.getString("country");
                    if(country == null){ country = "No Data"; }
                    xmlWriter.writeStartElement("Country");
                    xmlWriter.writeAttribute("Name", country);
                    xmlWriter.writeAttribute("Views", Integer.toString(rs.getInt(1)) );
                    
                    getOptionTwoXMLCity(xmlWriter, name, country, startDate, endDate);
                    xmlWriter.writeEndElement();
                }

                System.out.println("Country data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;

        }

	/**
         * getOptionTwoXMLCity - Finds cities within specified times, country and has viewed a specified algorithm name and outputs result into a XML file.
	 * @param XMLStreamWriter XMLStreamWriter that the results will be written to.
	 * @param String Algorithm name.
	 * @param String Country the cities are in.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
         * @return boolean of whether or not the query failed.
         */
        private boolean getOptionTwoXMLCity(XMLStreamWriter xmlWriter, String name, String country, Timestamp startDate, Timestamp endDate){
                 PreparedStatement ps = null;
            ResultSet rs = null;


            //for junit testing
            boolean failed = false;


            try{



                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(city), city FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (country = ?) AND (name = ?) AND (viewdate > ?) AND (viewdate <= ?) GROUP BY city ORDER BY city";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, country);
                    ps.setString(2, name);
                    ps.setTimestamp(3, startDate);
                    ps.setTimestamp(4, endDate);
                    rs = ps.executeQuery();



                System.out.println("Attempting to get city data");



                while(rs.next()){
                    String city = rs.getString("city");
                    if(city == null){ city = "No Data"; }
                    xmlWriter.writeStartElement("City");
                    xmlWriter.writeAttribute("Name", city);
                    xmlWriter.writeAttribute("Views", Integer.toString(rs.getInt(1)) );
                    xmlWriter.writeEndElement();
                }

                System.out.println("city data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;

        }


        /******************************************************************************

         Beginning XML Option Three Queries
         * Note: The first two queries are shared with Option 1

         ******************************************************************************/


	/**
         * getOptionTwoXMLCity - Finds users within specified times, country, and city and outputs result into a xml file.
	 * @param XMLStreamWriter XMLStreamWriter that the results will be written to.
	 * @param String Country the city is in.
	 * @param String City the users are in.
	 * @param Timestamp Represents the starting date of the search.
	 * @param Timestamp Represents the ending date of the search.
         * @return boolean of whether or not the query failed.
         */
        private boolean getOptionThreeXMLUser(XMLStreamWriter xmlWriter, String country, String city, Timestamp startDate, Timestamp endDate){
                 PreparedStatement ps = null;
            ResultSet rs = null;


            //for junit testing
            boolean failed = false;


            try{



                    //SQL Statement
                    String SQLQuery = "SELECT DISTINCT Count(IP.user), IP.user FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (country = ?) AND (city = ?) AND (viewdate > ?) AND (viewdate <= ?) GROUP BY IP.user ORDER BY IP.user";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, country);
                    ps.setString(2, city);
                    ps.setTimestamp(3, startDate);
                    ps.setTimestamp(4, endDate);
                    rs = ps.executeQuery();



                System.out.println("Attempting to get user data");


                while(rs.next()){
                    String user = rs.getString("IP.user");
                    if(user == null){ user = "No Data"; }
                    xmlWriter.writeStartElement("UserName");
                    xmlWriter.writeAttribute("Name", user);
                    xmlWriter.writeAttribute("Views", Integer.toString(rs.getInt(1)) );
                    xmlWriter.writeEndElement();
                }

                System.out.println("User data retrieved");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;

        }


        /******************************************************************************

         Beginning Delete Records Query

         ******************************************************************************/


	/**
         * deleteRecords - Deletes all records before a specified date.
	 * @param Timestamp - records before this date will be deleted.
         * @return boolean of whether or not the query failed.
         */
        public boolean deleteRecords(Timestamp deleteDate) throws SQLException{

            PreparedStatement ps = null;
            ResultSet rs = null;


            //for junit testing
            boolean failed = false;


            try{



                    //SQL Statement
                    String SQLQuery = "SELECT IP.ip, IP.user, IP.region, IP.city, IP.country FROM IP INNER JOIN Visualization ON IP.ip = Visualization.ip WHERE (viewdate <= ?)";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setTimestamp(1, deleteDate);

                    rs = ps.executeQuery();



                System.out.println("Attempting to delete data");



                while(rs.next()){
                    String ip = rs.getString("IP.ip");
                    String user = rs.getString("user");
                    String region = rs.getString("region");
                    String city = rs.getString("city");
                    String country = rs.getString("country");

                    deleteFromIPTable(ip, user, region, city, country);

                }

                deleteFromVisualizationTable(deleteDate);

                System.out.println("Data deleted");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;
            }

	/**
         * deleteFromIPTable - Deletes records from IP table that match the specified fields
	 * @param String - ip value.
	 * @param String - User name.
	 * @param String - Region.
	 * @param String - City.
	 * @param String - Country.
	 * @return Boolean value or whether the query failed.
         */
        private boolean deleteFromIPTable(String ip, String user, String region, String city, String country) throws SQLException{

            PreparedStatement ps = null;
            ResultSet rs = null;

            //for junit testing
            boolean failed = false;


            try{



                    //SQL Statement
                    String SQLQuery = "DELETE FROM IP WHERE (ip = ?) AND (user = ?) AND (region = ?) AND (city = ?) AND (country = ?)";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setString(1, ip);
                    ps.setString(2, user);
                    ps.setString(3, region);
                    ps.setString(4, city);
                    ps.setString(5, country);

                    ps.executeUpdate();



                System.out.println("Data deleted");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;
            }


	/**
         * deleteFromVisualizationTable - Deletes all records in visualization table that were created before a specified date.
	 * @param Timestamp - records before this date will be deleted.
         * @return boolean of whether or not the query failed.
         */
        private boolean deleteFromVisualizationTable(Timestamp deleteDate) throws SQLException{

            PreparedStatement ps = null;
            ResultSet rs = null;


            //for junit testing
            boolean failed = false;


            try{



                    //SQL Statement
                    String SQLQuery = "DELETE FROM Visualization WHERE (viewdate <= ?)";
                    conn.setAutoCommit(false);
                    ps = conn.prepareStatement(SQLQuery);


                    //Values for command
                    ps.setTimestamp(1, deleteDate);

                    ps.executeUpdate();



                System.out.println("Data deleted");

                    ps.close();
                    
                    }
            catch(Exception e) { e.printStackTrace(); failed = true;}
            return failed;
            }


        
}
